﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for D_ApproveGRN
/// </summary>
/// 
namespace Bestlife.ERP.DataAccess
{

    public class D_ApproveGRN
    {
        public static string Brand_Name;
        public static int lastid;
        public static decimal total;
        public static Boolean result = false;
        public D_ApproveGRN()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public static Bestlife.ERP.Entities.E_GRN getGRNdetails(string grnid)
        {
            SqlDataReader rdR;

            Bestlife.ERP.Entities.E_GRN grndetails = new Entities.E_GRN();
            try
            {

                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Oder_Date],[Book_GRNno],[User_Create],[Date_Create] FROM [Tbl_GRN] where [Or_ID]='" + grnid + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {

                    grndetails.Recevieddate = rdR["Book_GRNno"].ToString();
                    grndetails.Duedate = rdR["Oder_Date"].ToString();
                    grndetails.User = rdR["User_Create"].ToString();
                    grndetails.Datecreate = rdR["Date_Create"].ToString();
                    rdR.Close();
                }
                rdR.Close();
                SqlDataReader rdR1;
                String Ssql1 = "SELECT [Sup_Name],[Supplier_Bill_No] FROM [Tbl_SupplierOrder]  where [Or_ID]='" + grnid + "'";
                rdR1 = Connection.ReadTable(Ssql1);
                if (rdR1.Read())
                {
                    grndetails.Supplier = rdR1["Sup_Name"].ToString();
                    grndetails.Billno = rdR1["Supplier_Bill_No"].ToString();
                    rdR1.Close();

                }
                rdR1.Close();
                return grndetails;

            }



            catch
            {
            }

            return grndetails;

        }

        public static decimal grandtotal(string grnno)
        {

            SqlDataReader rdR;

            Bestlife.ERP.Entities.E_GRN grndetails = new Entities.E_GRN();
            try
            {

                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Total] FROM [Tbl_GRN]  where Or_ID='" + grnno + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    total = Convert.ToDecimal(rdR["Total"].ToString());


                    rdR.Close();
                }

            }
            catch
            {
            }
            return total;

        }

        public static decimal lastgrnnot_approve()
        {
            SqlDataReader rdR;
            try
            {

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT MAX(Or_ID) as lastid FROM Tbl_GRNrequest WHERE manger_status IS NULL";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    lastid = Convert.ToInt32(rdR["lastid"].ToString());

                }
                rdR.Close();
            }
            catch
            {
            }
            return lastid;
        }

        public static Boolean upd_supplier(Bestlife.ERP.Entities.E_PurchaseOrder grn, string user)
        {
            Boolean status = false;
            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

            string sql = "UPDATE [Tbl_SupplierOrder] SET [Sup_Name] ='" + grn.Supplier + "',[Contact] ='" + grn.Contrat + "',[User_Create] ='" + user + "',[Last_Modify] ='" + date + "' WHERE [Or_ID] ='" + grn.Or_ID1 + "'";
            Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
            if (edit)
            {
                status = true;

                return status;
            }

            return status;
        }

        public static Boolean updateorder(Bestlife.ERP.Entities.E_PurchaseOrder order, string user)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                string sql1 = "select count(Pro_ID) as count from Tbl_GRNrequest where Or_ID='" + order.Or_ID1 + "'";
                rdR = Connection.ReadTable(sql1);

                if (rdR.Read())
                {
                    int count = (int)rdR["count"];
                    rdR.Close();
                    string sql = "INSERT INTO [Tbl_GRN]([Or_ID],[Oder_Date],[Book_GRNno],[Date_Create],[TotalProduct],[User_Create]) VALUES('" + order.Or_ID1 + "','" + order.Issu + "','" + order.Due + "','" + date + "','" + count + "','" + user + "')";
                    Boolean add = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                    if (add)
                    {
                        Connection.con.Close();
                        rdR.Close();
                        result = true;

                    }
                    rdR.Close();
                }

            }
            catch
            {
                Connection.con.Close();


            }
            return result;

        }

        public static Boolean upd_GRN(Bestlife.ERP.Entities.E_PurchaseOrder order, string user)
        {
            SqlDataReader rdR;
            SqlDataReader rdR1;
            try
            {
                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                string sql1 = "select count(Pro_ID) as count from Tbl_GRNrequest where Or_ID='" + order.Or_ID1 + "'";
                rdR = Connection.ReadTable(sql1);
                if (rdR.Read())
                {
                    int count = (int)rdR["count"];
                    rdR.Close();
                    string sql2 = "SELECT [Total] FROM [Tbl_GRN] where [Or_ID]='" + order.Or_ID1 + "'";
                    rdR1 = Connection.ReadTable(sql2);
                    if (rdR1.Read())
                    {
                        decimal totalexit = Convert.ToDecimal(rdR1["Total"].ToString());
                        decimal totnew = totalexit + order.Total;
                        rdR1.Close();

                        string sql = "UPDATE [Tbl_GRN] SET [TotalProduct] ='" + count + "',[Total] ='" + totnew + "',[Last_Modify] ='" + date + "' WHERE [Or_ID] = '" + order.Or_ID1 + "'";
                        Boolean add = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                        if (add)
                        {
                            Connection.con.Close();
                            rdR.Close();
                            result = true;

                        }
                        rdR.Close();
                    }
                }
            }
            catch
            {
                Connection.con.Close();


            }
            return result;

        }

        public static Boolean upd_GRNbytot(Bestlife.ERP.Entities.E_PurchaseOrder order, string user)
        {
            SqlDataReader rdR;
            SqlDataReader rdR1;
            try
            {
                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                string sql1 = "select count(Pro_ID) as count from Tbl_GRNrequest where Or_ID='" + order.Or_ID1 + "'";
                rdR = Connection.ReadTable(sql1);
                if (rdR.Read())
                {
                    int count = (int)rdR["count"];
                    rdR.Close();



                    decimal totnew = order.Total;


                    string sql = "UPDATE [Tbl_GRN] SET [TotalProduct] ='" + count + "',[Total] ='" + totnew + "',[Last_Modify] ='" + date + "' WHERE [Or_ID] = '" + order.Or_ID1 + "'";
                    Boolean add = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                    if (add)
                    {
                        Connection.con.Close();
                        rdR.Close();
                        result = true;

                    }
                    rdR.Close();
                }
            }

            catch
            {
                Connection.con.Close();


            }
            return result;

        }

        public static string select_product(string pro_id)
        {
          
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Brand_Name]FROM [Tbl_Product] where [Pro_ID]='" + pro_id + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    Brand_Name = rdR["Brand_Name"].ToString();
         
                }
                rdR.Close();
            }
            catch
            {
            }

            return Brand_Name;

        }

     

    }
        }

      

   